package org.MobileSales.DataManagement;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.MobileSales.Model.Clients.Client;
import org.MobileSales.Model.Clients.ClientGroup;
import org.MobileSales.Model.Products.Category;
import org.MobileSales.Model.Params.Param;
import org.MobileSales.Model.Params.ParamObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ClientsManager {
	public Context context;
	
	public ClientsManager(Context c)
	{
		this.context = c;
	}
	
	public Client GetClient(int clientId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, ClientGroupId, Name, Name2, Nip, Zipcode, City, Street, Phone, Fax, Info, Info2, Debts, IsSupplier, LastVisitTime" 
							+ " FROM Clients" 
							+ " WHERE _id = " + clientId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Client result = new Client();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Group = GetClientGroup(c.getInt(c.getColumnIndex("ClientGroupId")));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Name2 = c.getString(c.getColumnIndex("Name2"));
     		result.Nip = c.getString(c.getColumnIndex("Nip"));
     		result.Zipcode = c.getString(c.getColumnIndex("Zipcode"));
     		result.City = c.getString(c.getColumnIndex("City"));
     		result.Street = c.getString(c.getColumnIndex("Street"));
     		result.Phone = c.getString(c.getColumnIndex("Phone"));
     		result.Fax = c.getString(c.getColumnIndex("Fax"));
     		result.Info = c.getString(c.getColumnIndex("Info"));
     		result.Info2 = c.getString(c.getColumnIndex("Info2"));
     		result.Debts = c.getDouble(c.getColumnIndex("Debts"));
     		result.IsSupplier = c.getInt(c.getColumnIndex("IsSupplier")) == 1 ? true : false;
     		result.LastVisitTime = c.getString(c.getColumnIndex("LastVisitTime"));
     		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public void StoreClient(Client client)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
			
			cv.put("ClientGroupId", client.Group.Id);
			cv.put("Name",client.Name);
			cv.put("Name2", client.Name2);
			cv.put("Nip", client.Nip);
			cv.put("Zipcode", client.Zipcode);
			cv.put("City", client.City);
			cv.put("Street", client.Street);
			cv.put("Phone", client.Phone);
			cv.put("Fax", client.Fax);
			cv.put("Info", client.Info);
			cv.put("Info2", client.Info2);
			cv.put("Debts", client.Debts);
			cv.put("IsSupplier", client.IsSupplier);
			cv.put("LastVisitTime", client.LastVisitTime);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (client.Id != 0)
				db.update("Clients", cv, "_id ="+client.Id, null);
			else
				db.insert("Clients", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveClient(int clientId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("Clients", "_id ="+clientId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public Map<Param, String> GetClientParams(int clientId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT ParamId, Value"
	    			+ " FROM ClientHasParams" 
	    			+ " WHERE ClientId = " + clientId;
     		Cursor c = db.rawQuery(query, null);
     		
     		Map<Param, String> result = new HashMap<Param,String>();
    		if (c.moveToFirst()){
    			ParamsManager pm = new ParamsManager(this.context);
	     		while (c!=null){    	     		
	         		result.put(pm.GetParam(c.getInt(c.getColumnIndex("ParamId"))), c.getString(c.getColumnIndex("Value")));
	         		c.moveToNext();
	     		}
	     	}
    		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;		
	}
	
	public void StoreClientParams(int clientId, Map<Param,String> paramMap)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
     		String query = "SELECT ParamId"
    			+ " FROM ClientHasParams" 
    			+ " WHERE ClientId = " + clientId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> paramsId = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (c!=null){    	     		
	         		paramsId.add(c.getInt(c.getColumnIndex("ParamId")));
	         		c.moveToNext();
	     		}
	     	}
	 		List<Integer> addedParams = new ArrayList<Integer>();
	 		for (Param key : paramMap.keySet()){
	 			addedParams.add(key.Id);
	 			boolean exists = paramsId.contains(key.Id);
	 			
				ContentValues cv = new ContentValues();
				
				cv.put("ClientId", clientId);
				cv.put("ParamId",key.Id);
				cv.put("Value", paramMap.get(key));
	
				if (exists)
					db.update("ClientHasParams", cv, "ClientId ="+clientId + " and ParamId =" + key.Id, null);
				else
					db.insert("ClientHasParams", null, cv);
	 		}
	 		
	 		for (int paramId : paramsId)
	 		{
	 			if (!addedParams.contains(paramsId))
		 	     	try {
		 				db.delete("ClientHasParams", "ClientId ="+clientId + " and ParamId =" + paramId, null);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}		
	}
	
	
	public ClientGroup GetClientGroup(int clientGroupId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, Name, Discount" 
							+ " FROM ClientGroups" 
							+ " WHERE _id = " + clientGroupId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		ClientGroup result = new ClientGroup();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Discount = c.getDouble(c.getColumnIndex("Discount"));
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	public void StoreClientGroup(ClientGroup clientGroup)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
			
			cv.put("Name",clientGroup.Name);
			cv.put("Discount", clientGroup.Discount);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (clientGroup.Id != 0)
				db.update("ClientGroups", cv, "_id ="+clientGroup.Id, null);
			else
				db.insert("ClientGroups", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveClientGroup(int clientGroupId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("ClientGroups", "_id ="+clientGroupId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}
	}
}
